import re
from openpyxl import load_workbook
import copy
from WordDB import WordSql

class wordcourse():
    def __int__(self):
        self.userid = 0
        self.wdsql = None
        self.coursestileyuan = []
        self.stname = []
        self.templatesname = []
        self.coursepath = ''

    def initdata(self):
        self.wdsql = WordSql('127.0.0.1', 'root', 'cai1220...', 3306, 'worddb', 'utf8')
        self.stname = ['通识课', '通识选修课', '学科基础课', '学科专业基础课', '专业选修课', '集中性实践', '第二课堂']
        self.coursepath= "d:\\\\coursexlsx\\\\"

        self.templatesname = ['目标', '要求', '预期', '主干学科', '主要实践性教学环节', '专业核心课程']
        self.coursestileyuan = [
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业', '分组统计'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注', '毕业要求'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注')]
        self.createtbcourses()

    def insertcoursetemplates(self):
        userinfo = self.selectuserdictinfo(self.userid)
        coursetemplatedictinfo = self.selectcoursetemplates()
        coursepath = self.coursepath +str(userinfo.get("userid")) + userinfo.get("major") + "course"
        if coursetemplatedictinfo != -1:
            coursepath = self.coursepath + str(userinfo.get("userid")) + userinfo.get("major") + "course"+".xlsx"
            sql = "replace coursetemplates (coursetemplatesid,userid,major,coursepath)values({coursetemplatesid},{userid},'{major}','{coursepath}')".format(
                coursetemplatesid=coursetemplatedictinfo.get('coursetemplatesid'), userid=userinfo.get("userid"),
                major=userinfo.get('major'), coursepath=coursepath
            )
        else:
            sql = "replace coursetemplates (userid,major,coursepath)values({userid},'{major}','{coursepath}')".format(
                userid=userinfo.get("userid"), major=userinfo.get('major'), coursepath=coursepath
            )
        self.wdsql.insert(sql)

    def updateusermajor(self, userid, major):
        sql = "update user set major= '{a}' where userid = {b}".format(b=userid, a=major)
        self.wdsql.update(sql)

    def createtbcourses(self):
        sqlcreatecoursetemplates = "create table if not exists coursetemplates (coursetemplatesid int (10) auto_increment primary key," \
                                   "userid int(10),major char(30),coursepath TEXT default null)default charset=utf8;"
        self.wdsql.create(sqlcreatecoursetemplates)
    def selectcoursetemplates(self):
        user = self.selectuserdictinfo(self.userid)
        sql = "select * from coursetemplates where userid = {userid} and major = '{major}'".format(
            userid=user.get("userid"), major=user.get('major'))
        coursetemplatesdictinfo = self.wdsql.get_onedict(sql)
        if coursetemplatesdictinfo == None:
            return -1
        return coursetemplatesdictinfo

    def selectuserdictinfo(self, userid):
        sql = "select * from user where userid={}".format(userid)
        userinfo = self.wdsql.get_onedict(sql)
        if userinfo == None:
            return -1
        return userinfo

    def selecttemplatesdict(self, major):
        sql = "select *from templates where major='{major}'".format(major=major)
        print(sql)
        templatesdictinfo = self.wdsql.get_onedict(sql)
        if templatesdictinfo == None:
            templatesdictinfo = dict.fromkeys(self.templatesname, "")
            return templatesdictinfo
        return templatesdictinfo

    def selecttemplatesdictinfo(self, major):
        sql = "select *from templates where major='{major}'".format(major=major)
        print(sql)
        templatesdictinfo = self.wdsql.get_onedict(sql)
        if templatesdictinfo == None:
            return -1
        return templatesdictinfo

    def selectuserlistinfo(self, userid):
        sql = self.cur.mogrify("select * from user where userid=%s", userid)
        userinfo = self.wdsql.get_all(sql)
        if userinfo == ():
            return -1
        tmp = []
        for i in userinfo:
            tmp.extend(i)
        userinfo = tmp
        self.LoadUDictUserInfo(userinfo)
        return self.dictuserinfo


if __name__ == '__main__':
    wc = wordcourse()
